﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Ex11.GetAllMoneyRetrivalsForCard
{
    public class GetAllMoneyRetrivals
    {
        private const string CONNECTION_STRING = "Server=.\\SQLEXPRESS;" +
" Database=ATM; Integrated Security=true";

        public static void Main()
        {
            string cardNumber = "12aa21a";
            int cardPIN = 1234;

            SqlConnection conDB = new SqlConnection(CONNECTION_STRING);
            conDB.Open();
            using (conDB)
            {
                SqlCommand extractAllRetrievals = conDB.CreateCommand();
                extractAllRetrievals.CommandText =
                  @"SELECT * FROM AccountTransactions a
                    WHERE a.CardNumber = 
                    (
                    SELECT c.CardNumber FROM CardAccounts c
                    WHERE c.CardPIN = @cardPIN
                    ) AND a.CardNumber = @cardNumber
                    ORDER BY a.TransactionDate";
                extractAllRetrievals.Parameters.Add(new SqlParameter("@cardPIN", cardPIN));
                extractAllRetrievals.Parameters.Add(new SqlParameter("@cardNumber", cardNumber));
                SqlDataReader reader = extractAllRetrievals.ExecuteReader();
                using (reader)
                {
                    if (reader.Read())
                    {
                        Console.WriteLine("{0,-5}{1,-24}{2}","ID","Date","Ämount");

                        int accountTransferID = (int)reader["AccountTransID"];
                        DateTime transactionDate = (DateTime)reader["TransactionDate"];
                        decimal amount = (decimal)reader["Amount"];
                        Console.WriteLine("{0,-5}{1,-24}{2}", accountTransferID, transactionDate, amount);

                        while (reader.Read())
                        {
                            accountTransferID = (int)reader["AccountTransID"];
                            transactionDate = (DateTime)reader["TransactionDate"];
                            amount = (decimal)reader["Amount"];
                            Console.WriteLine("{0,-5}{1,-24}{2}", accountTransferID, transactionDate, amount);
                        }
                    }
                    else
                    {
                        Console.WriteLine("No retrievals are available for Card:{0} with PIN:{1}!",
                            cardNumber, cardPIN);
                    }
                }
            }
        }
    }
}
